/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dal;

import connection.ConnectionDB;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Flight;

/**
 *
 * @author Administrator
 */
public class FlightDAL {

    private ConnectionDB conn = new ConnectionDB();
    private CallableStatement cs;

    public List<Flight> listAllFight() {
        try {
            List<Flight> list = new ArrayList<Flight>();
            Flight flight;
            cs = conn.createConnection().prepareCall("{call sp_ListAllFlight}");
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                flight = new Flight(rs.getInt("FlightID"), rs.getString("FlightName"), rs.getString("DepartTime"), rs.getString("ArrivalTime"), rs.getInt("DepartLocation"), rs.getInt("ArrivalLocation"));
                list.add(flight);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(FlightDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public boolean newFlight(Flight flight) {
        try {
            cs = conn.createConnection().prepareCall("{call sp_NewFlight(?,?,?,?,?)}");
            cs.setString("flightname", flight.getFlightName());
            cs.setString("departtime", flight.getDepartTime());
            cs.setString("arrivaltime", flight.getArrTime());
            cs.setInt("departlocal", flight.getDepartLocal());
            cs.setInt("arrivallocal", flight.getArrLocal());

            if (cs.executeUpdate() == 1) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(FlightDAL.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean updateFlight(Flight flight) {
        try {
            cs = conn.createConnection().prepareCall("{call sp_UpdateFlight(?,?,?,?,?,?)}");
            cs.setInt("flightid", flight.getFlightID());
            cs.setString("flightname", flight.getFlightName());
            cs.setString("departtime", flight.getDepartTime());
            cs.setString("arrivaltime", flight.getArrTime());
            cs.setInt("departlocal", flight.getDepartLocal());
            cs.setInt("arrivallocal", flight.getArrLocal());

            if (cs.executeUpdate() == 1) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(FlightDAL.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public List<Flight> searchFlightByLocation(String localName) {
        try {
            List<Flight> list = new ArrayList<Flight>();
            Flight flight;
            cs = conn.createConnection().prepareCall("{call sp_SearchFlightByLocation(?)}");
            cs.setString("local", localName);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                flight = new Flight(rs.getInt("FlightID"), rs.getString("FlightName"), rs.getString("DepartTime"), rs.getString("ArrivalTime"), rs.getInt("DepartLocation"), rs.getInt("ArrivalLocation"));
                list.add(flight);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(FlightDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Flight> searchFlightByName(String flightName) {
        try {
            List<Flight> list = new ArrayList<Flight>();
            Flight flight;
            cs = conn.createConnection().prepareCall("{call sp_SearchFlightByName(?)}");
            cs.setString("name", flightName);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                flight = new Flight(rs.getInt("FlightID"), rs.getString("FlightName"), rs.getString("DepartTime"), rs.getString("ArrivalTime"), rs.getInt("DepartLocation"), rs.getInt("ArrivalLocation"));
                list.add(flight);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(FlightDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Flight> searchFlightByDate(String dateFrom, String dateTo) {
        try {
            List<Flight> list = new ArrayList<Flight>();
            Flight flight;
            cs = conn.createConnection().prepareCall("{call sp_SearchFlightByDate(?,?)}");
            cs.setString("dateFrom", dateFrom);
            cs.setString("dateTo", dateTo);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                flight = new Flight(rs.getInt("FlightID"), rs.getString("FlightName"), rs.getString("DepartTime"), rs.getString("ArrivalTime"), rs.getInt("DepartLocation"), rs.getInt("ArrivalLocation"));
                list.add(flight);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(FlightDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Flight> advanceSearchFlight(String departLocal, String arrivalLocal,String dateFrom, String dateTo) {
        try {
            List<Flight> list = new ArrayList<Flight>();
            Flight flight;
            cs = conn.createConnection().prepareCall("{call sp_advanceSearchFlight(?,?,?,?)}");
            cs.setString("departlocal", departLocal);
            cs.setString("arrivallocal", arrivalLocal);
            cs.setString("departtime", dateFrom);
            cs.setString("arrivaltime", dateTo);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                flight = new Flight(rs.getInt("FlightID"), rs.getString("FlightName"), rs.getString("DepartTime"), rs.getString("ArrivalTime"), rs.getInt("DepartLocation"), rs.getInt("ArrivalLocation"));
                list.add(flight);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(FlightDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
}
